package com.gdk.ksoft.assembly.dbhelper;

import com.gdk.ksoft.data.entry.tables.GoodsBrandTable;
import com.gdk.ksoft.data.entry.tables.GoodsGroupDetailTable;
import com.gdk.ksoft.data.entry.tables.GoodsGroupTable;
import com.gdk.ksoft.data.entry.tables.GoodsTable;
import com.gdk.ksoft.data.entry.tables.OneGoodsTypesTable;
import com.gdk.ksoft.data.entry.tables.ShoppingCartGoodsTable;
import com.gdk.ksoft.data.entry.tables.UnsoldGoodsTable;
import com.lidroid.xutils.DbUtils;
import com.lidroid.xutils.db.sqlite.Selector;
import com.lidroid.xutils.db.sqlite.SqlInfo;
import com.lidroid.xutils.db.table.DbModel;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;

/**
 * Created by hel on 2014-12-17.
 * 查找商品的数据库辅助类
 */
public class FfindGoodsDbHelper {

    //==================================查找商品品牌数据=======================================================

    /**
     * 查询所有商品品牌数据
     * @return
     * @throws Exception
     */
    public static List<GoodsBrandTable> findAllGoodsBrands(DbUtils dbUtils)throws Exception{
        return dbUtils.findAll(GoodsBrandTable.class);
    }


    /**
     * 根据商品品牌ID查询商品
     * @param brandId
     * @return
     * @throws Exception
     */
    public static List<GoodsTable> findAllGoodsByBrandId(final DbUtils dbUtils,int brandId)throws Exception{
        return dbUtils.findAll(Selector.from(GoodsTable.class).where("pp_id","=",brandId));
    }

    //==================================查找商品分组数据=======================================================

    /**
     * 查询所有商品分组数据
     * @return
     * @throws Exception
     */
    public static List<GoodsGroupTable> findAllGoodsGroups(final DbUtils dbUtils)throws Exception{
        return dbUtils.findAll(GoodsGroupTable.class);
    }


    /**
     * 根据商品分组ID查询商品
     * @param groupId
     * @return
     * @throws Exception
     */
    public static List<GoodsTable> findAllGoodsByGroupId(final DbUtils dbUtils,int groupId)throws Exception{
        List<GoodsGroupDetailTable> goodsGroupDetailTableList = dbUtils.findAll(Selector.from(GoodsGroupDetailTable.class).where("fz_id", "=", groupId));
        List<GoodsTable> goodslist = null;
        if(goodsGroupDetailTableList!=null && goodsGroupDetailTableList.size()>0){
            goodslist = new ArrayList<>();
            for(int i=0;i<goodsGroupDetailTableList.size();i++){
                int sp_id = goodsGroupDetailTableList.get(i).sp_id;
                GoodsTable item = dbUtils.findFirst(Selector.from(GoodsTable.class).where("id","=",sp_id));
                if(item == null) continue;
                goodslist.add(item);
            }
        }
        return goodslist;
    }

    //===================================查找同品多味的商品=============================================================

    /**
     * 查询所有同品多味商品分组数据
     * @return
     * @throws Exception
     */
    public static List<OneGoodsTypesTable> findAllGoodsTpdw(final DbUtils dbUtils)throws Exception{
        return dbUtils.findAll(OneGoodsTypesTable.class);
    }

    /**
     * 通过同品多味的商品分组ID查询商品集合
     * @return
     */
    public static List<ShoppingCartGoodsTable> findAllGoodsByTpdwId(DbUtils dbUtils,int fzId,int kh_id,int shdz_id)throws Exception{
        String sql = "SELECT DISTINCT p.id,p.mc,p.tm,p.gg,p.dw,pj.jldwmc,pj.dj,pj.hsgx,p.csrq,p.bzq,p.slt "
                + "FROM shangpin p,shangpin_jldw pj,tpdw t,tpdw_sp ts "
                + "WHERE p.id = pj.sp_id AND p.id = ts.sp_id AND t.id = ts.fz_id AND ts.fz_id = " + fzId + ";";
        List<DbModel> dbModels = dbUtils.findDbModelAll(new SqlInfo(sql));
        if(dbModels == null && dbModels.size()<=0) return null;
        List<ShoppingCartGoodsTable> shopinglist = new ArrayList<>();
        for(DbModel item : dbModels){
            ShoppingCartGoodsTable pitem = new ShoppingCartGoodsTable();
            pitem.id = 0;//字段ID默认为0
            pitem.kh_id = kh_id;
            pitem.shdz_id = shdz_id;
            pitem.sp_id = item.getInt("id");//获得商品ID
            pitem.xsdw = item.getString("jldwmc");//获得计量单位名称
            pitem.hsdw = item.getString("dw");//获得核算单位
            pitem.lx = 0;//默认商品类型为0-正常
            pitem.hsgx = item.getInt("hsgx");//获取商品换算关系
            pitem.mc = item.getString("mc");//获取商品名称
            pitem.tm = item.getString("tm");//获取商品条码
            pitem.gg = item.getString("gg");//获取商品规格
            pitem.slt = item.getString("slt");//获取商品缩略图
            pitem.hsdw = item.getString("dw");//获取最小单位
            pitem.xsdw = item.getString("jldwmc");//获取商品计量单位名称
            pitem.xsyj = item.getFloat("dj");//获取销售单位价
            pitem.bzxsdwj = item.getFloat("dj");//获取标准销售单位价
            pitem.bzlsj = pitem.bzxsdwj/pitem.hsgx;//标准零售价
            pitem.zdsj = item.getString("csrq");//获取商品生产日期
            pitem.xgbs = 1;//默认价格是可以修改的
            int bzq = item.getInt("bzq");//获取保质期天数
            UnsoldGoodsTable unsoldInfo = null;
            if(bzq!=0){//查询保质期数据
                unsoldInfo = dbUtils.findFirst(Selector.from(UnsoldGoodsTable.class)
                        .where("sp_id","=",pitem.sp_id).orderBy("dlrq"));
                if(unsoldInfo != null) {//修改下面的参数
                    bzq = unsoldInfo.bzts;//修改保质期天数数据
                    pitem.zdsj = unsoldInfo.csrq;//获取其生成日期
                }
            }
            pitem.bzq = bzq;
            shopinglist.add(pitem);
        }
        return shopinglist;
    }

    //===============================================车销请货数据查询=================================================

    /**
     * 查询某品牌下所有车销请货的商品
     * @param dbUtils
     * @param brandId  品牌ID
     * @return
     */
    public static List<HashMap<String,Object>> findAllCxqhGoodsByBrandId(final DbUtils dbUtils,final int brandId)throws Exception{
        String sql = "SELECT DISTINCT a.id,a.mc,a.tm,IFNULL(a.kc1,0) kc2,IFNULL(s.hsgx,1) hsgx1,a.slt pic,a.gg,a.dw " +
                "FROM (SELECT p.id,p.mc,p.tm,p.pp_id,p.slt,IFNULL(c.kc,0) kc1,p.gg,p.dw " +
                      "FROM shangpin p LEFT JOIN cxqh c ON p.id = c.sp_id) a,pinpai b,shangpin_jldw s " +
                "WHERE s.sp_id = a.id AND a.pp_id = b.id AND a.pp_id = " + brandId + ";";
        List<DbModel> dbModelList = dbUtils.findDbModelAll(new SqlInfo(sql));
        if(dbModelList == null || dbModelList.size()<=0) return null;
        List<HashMap<String,Object>> resultList = new ArrayList<>();
        for(DbModel item : dbModelList){
            HashMap<String,Object> itemMap = new HashMap<>();
            itemMap.put("sp_id",item.getInt("a.id"));
            itemMap.put("sp_mc",item.getString("a.mc"));
            itemMap.put("sp_tm",item.getString("a.tm"));
            itemMap.put("sp_hsgx",item.getInt("hsgx1"));
            itemMap.put("sp_kc",item.getFloat("kc2")/item.getInt("hsgx1"));
            itemMap.put("sp_pic",item.getString("pic"));
            itemMap.put("sp_gg",item.getString("a.gg")+item.getString("a.dw"));
            itemMap.put("js",0);
            itemMap.put("ss",0);
            resultList.add(itemMap);
        }
        return resultList;
    }


    /**
     * 查询某关键字下所有车销请货的商品
     * @param dbUtils
     * @param keyword  关键字
     * @return
     */
    public static List<HashMap<String,Object>> findAllCxqhGoodsByKeyword(final DbUtils dbUtils,final String keyword)throws Exception{
        String sql = "SELECT DISTINCT a.id,a.mc,a.tm,IFNULL(a.kc1,0) kc2,IFNULL(s.hsgx,1) hsgx1,a.kjj,a.gg,a.slt pic,a.dw " +
                "FROM (SELECT p.id,p.mc,p.tm,p.pp_id,IFNULL(c.kc,0) kc1,p.kjj,p.gg,p.slt,p.dw " +
                "FROM shangpin p LEFT JOIN cxqh c ON p.id = c.sp_id) a,shangpin_jldw s " +
                "WHERE s.sp_id = a.id AND ( a.mc LIKE '%" + keyword + "%' OR a.tm LIKE '%"
                + keyword + "%' OR a.gg LIKE '%" + keyword + "%' OR a.kjj LIKE '%" + keyword + "%');";
        List<DbModel> dbModelList = dbUtils.findDbModelAll(new SqlInfo(sql));
        if(dbModelList == null || dbModelList.size()<=0) return null;
        List<HashMap<String,Object>> resultList = new ArrayList<>();
        for(DbModel item : dbModelList){
            HashMap<String,Object> itemMap = new HashMap<>();
            itemMap.put("sp_id",item.getInt("a.id"));
            itemMap.put("sp_mc",item.getString("a.mc"));
            itemMap.put("sp_tm",item.getString("a.tm"));
            itemMap.put("sp_hsgx",item.getInt("hsgx1"));
            itemMap.put("sp_kc",item.getFloat("kc2")/item.getInt("hsgx1"));
            itemMap.put("sp_pic",item.getString("pic"));
            itemMap.put("sp_gg",item.getString("a.gg")+item.getString("a.dw"));
            itemMap.put("js",0);
            itemMap.put("ss",0);
            resultList.add(itemMap);
        }
        return resultList;
    }



    /**
     * 查询所有车销请货购物车的商品
     * @param dbUtils
     * @return
     */
    public static List<HashMap<String,Object>> findAllCxqhGoodsInCart(final DbUtils dbUtils)throws Exception{
        String sql = "SELECT DISTINCT c.id,c.sp_id,p.gg sp_gg,p.dw sp_dw,p.slt sp_pic,p.mc,p.tm,IFNULL(d.hsgx,1) sp_hsgx," +
                "IFNULL(c.js,0) sp_js,IFNULL(c.ss,0) sp_ss " +
                "from cxqh_spcart c,shangpin p,shangpin_jldw d " +
                " WHERE c.sp_id = p.id AND p.id = d.sp_id ORDER BY c.id DESC;";
        List<DbModel> dbModelList = dbUtils.findDbModelAll(new SqlInfo(sql));
        if(dbModelList == null || dbModelList.size()<=0) return null;
        List<HashMap<String,Object>> resultList = new ArrayList<>();
        for(DbModel item : dbModelList){
            HashMap<String,Object> itemMap = new HashMap<>();
            itemMap.put("data_id",item.getInt("id"));
            itemMap.put("sp_id",item.getInt("sp_id"));
            itemMap.put("sp_mc",item.getString("mc"));
            itemMap.put("sp_tm",item.getString("tm"));
            itemMap.put("sp_hsgx",item.getInt("sp_hsgx"));
            itemMap.put("sp_gg",item.getString("sp_gg")+item.getString("sp_dw"));
            itemMap.put("sp_pic",item.getString("sp_pic"));
            itemMap.put("js",item.getInt("sp_js"));
            itemMap.put("ss",item.getInt("sp_ss"));
            resultList.add(itemMap);
        }
        return resultList;
    }

}
